﻿using Aspfm.Main;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Aspfm.Dal
{
    class SQLiteDB : IDbHelper
    {
        public SQLiteDB(string conn)
        {
            connstr = conn;
        }
        public override DataSourceType GetDbType()
        {
            return DataSourceType.SQLite;
        }
        internal override IDbConnection GetNewConn()
        {
            string current = BaseAshx.CurrentPath();
            if (current.EndsWith("exeApplication"))
            {
                current = System.IO.Directory.GetParent(current).FullName;
            }
            connstr = connstr.Replace("..", current);
            return new SQLiteConnection(connstr);
        }
        internal override IDbDataAdapter GetDataAdapter()
        {
            return new SQLiteDataAdapter();
            //throw new Exception("");
        }
        internal override IDataParameter ParamInit(string paramName, object value, string colType = "VARCHAR2", ParameterDirection paramDir = ParameterDirection.Input, int length = 200)
        {
            //throw new Exception("");
            SQLiteParameter iparam = new SQLiteParameter();
            if (paramDir != ParameterDirection.Input)
            {
                iparam.Size = length;
            }
            iparam.Direction = paramDir;
            iparam.ParameterName = paramName;
            iparam.Value = value;
            iparam.DbType = GetDataTypeByType(colType);
            if (iparam.Value != null)
            {
                iparam.Value = CheckValueType(colType, value, paramName);
            }
            if (iparam.Value == null) iparam.Value = "";
            return iparam;
        }
        public DbType GetDataTypeByType(string sqlTypeString = "VARCHAR2")
        {
            DbType dbType = DbType.AnsiString;//默认为Object
            sqlTypeString = sqlTypeString.Replace(" ", "").Replace("_", "").Replace("2", "").ToLower();
            switch (sqlTypeString)
            {
                case "int":
                    dbType = DbType.Int32;
                    break;
                case "varchar":
                    dbType = DbType.AnsiString;
                    break;
                case "bit":
                    dbType = DbType.Byte;
                    break;
                case "datetime":
                case "date":
                    dbType = DbType.DateTime;
                    break;
                case "decimal":
                    dbType = DbType.Decimal;
                    break;
                case "float":
                    dbType = DbType.Decimal;
                    break;
                case "image":
                    dbType = DbType.Byte;
                    break;
                case "ntext":
                    dbType = DbType.AnsiString;
                    break;
                case "nvarchar":
                    dbType = DbType.AnsiString;
                    break;
                case "smallint":
                    dbType = DbType.Int16;
                    break;
                case "text":
                    dbType = DbType.AnsiString;
                    break;
                case "bigint":
                    dbType = DbType.Int64;
                    break;
                case "binary":
                    dbType = DbType.Binary;
                    break;
                case "char":
                    dbType = DbType.AnsiString;
                    break;
                case "nchar":
                    dbType = DbType.AnsiString;
                    break;
                case "numeric":
                    dbType = DbType.Decimal;
                    break;
                case "tinyint":
                    dbType = DbType.Int16;
                    break;
                case "varbinary":
                    dbType = DbType.Binary;
                    break;
                case "xml":
                    dbType = DbType.Xml;
                    break;
                default:
                    throw new Exception("not find DbType:" + sqlTypeString);
            }
            return dbType;
        }

        internal override DataSet LaodAllColType(string tableName, string columnName = null)
        {
            DataSet dsallcols = QuerySql("PRAGMA table_info(" + tableName + ")");
            if (DbFactory.CheckDSHadRow(dsallcols))
            {
                dsallcols.Tables[0].Columns["Name"].ColumnName = "column_name";
                dsallcols.Tables[0].Columns["Type"].ColumnName = "data_type";

                for (int i = dsallcols.Tables[0].Rows.Count - 1; i >= 0; i--)
                {
                    string datatype = dsallcols.Tables[0].Rows[i]["data_type"].ToString().ToUpper();
                    int index = datatype.IndexOf('(');
                    if (index > 0)
                    {
                        dsallcols.Tables[0].Rows[i]["data_type"] = datatype.Substring(0, index);
                    }

                    if (!string.IsNullOrEmpty(columnName))
                    {
                        if (dsallcols.Tables[0].Rows[i]["column_name"].ToString().ToUpper() != columnName.ToUpper())
                        {
                            dsallcols.Tables[0].Rows.RemoveAt(i);
                        }
                    }
                }
                return dsallcols;
            }
            return null;
        }

        public override bool ExistTableOrView(string tableName)
        {
            //(SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master)  临时表
            return QuerySqlCount("select name from sqlite_master where type in ('table','view') and UPPER(name)='" + tableName.Trim().ToUpper() + "'") > 0;
        }

        public override DataSet LaodProcParamsType(string procName)
        {
            return null;
        }

        internal override string GetParamsName(string procName)
        {
            if (procName.StartsWith("$"))
            {
                return procName;
            }
            return "$p_" + procName;
        }

        public override DataSet LaodProcAll()
        {
            return null;
        }
    }
}
